﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using System.Configuration;
using System.IO;

namespace MyTools
{
    public class DBManage
    {
        private static string connectionString;

        //获取连接字符串，参数为服务器、数据库、用户名、密码
        public static void connectToServer(string server, string database, string user, string pwd)
        {

            string con = "server=" + server + ";database=" + database + ";uid=" + user + ";pwd=" + pwd;
            DBManage.connectionString = con;
        }

        //获取连接字符串，参数为服务器、数据库、用户名、密码
        public static void connectToServer(string con)
        {

            DBManage.connectionString = con;
        }



        //执行SQL语句，返回影响的记录数
        public static int ExecuteSql(string SQLString, string SQLConnectionString)
        {
            using (SqlConnection connection = new SqlConnection(SQLConnectionString))
            {
                SqlCommand cmd = new SqlCommand(SQLString, connection);

                try
                {
                    if (!string.IsNullOrEmpty(SQLString))
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    return 0;
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    connection.Close();
                    throw new Exception(E.Message);
                }
            }

        }

        //执行多条SQL语句，实现数据库事务。
        public static void ExecuteSqlTran(ArrayList SQLStringList, string SQLConnectionString)
        {
            using (SqlConnection conn = new SqlConnection(SQLConnectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }

        //执行一条计算查询结果语句，返回查询结果（object）。
        public static object GetSingle(string SQLString, string SQLConnectionString)
        {
            using (SqlConnection connection = new SqlConnection(SQLConnectionString))
            {
                SqlCommand cmd = new SqlCommand(SQLString, connection);

                try
                {
                    connection.Open();
                    object obj = cmd.ExecuteScalar();
                    if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    connection.Close();
                    throw new Exception(e.Message);
                }
            }
        }


        //执行查询语句，返回SqlDataReader.
        public static SqlDataReader ExecuteReader(string strSQL)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand(strSQL, connection);

                try
                {
                    connection.Open();
                    SqlDataReader myReader = cmd.ExecuteReader();
                    return myReader;
                }
                catch (System.Data.SqlClient.SqlException e)
                {
                    throw new Exception(e.Message);
                }
            }
        }

        //执行查询语句，返回DataSet.
        public static DataSet Query(string SQLString, string SQLConnectionString)
        {
            DataSet ds = new DataSet();
            using (SqlConnection connection = new SqlConnection(SQLConnectionString))
            {

                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
            }
            return ds;

        }


        //参数为字段名和表名，返回最大值
        //public static int GetMaxID(string FieldName, string TableName)
        //{
        //    string strsql = "select max (" + FieldName + ") from " + TableName;
        //    object obj = GetSingle(strsql);
        //    if (obj == null)
        //    {
        //        return 1;
        //    }
        //    else
        //    {
        //        return int.Parse(obj.ToString());
        //    }
        //}

        //构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)，参数为数据库连接、存储过程名、存储过程参数。
        public static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            foreach (SqlParameter parameter in parameters)
            {
                command.Parameters.Add(parameter);
            }
            return command;
        }

        //创建 SqlCommand 对象实例(用来返回一个整数值),参数为数据库连接、存储过程名、存储过程参数。
        public static SqlCommand BuildIntCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
            command.Parameters.Add(new SqlParameter("ReturnValue",
                SqlDbType.Int, 4, ParameterDirection.ReturnValue,
                false, 0, 0, string.Empty, DataRowVersion.Default, null));
            return command;
        }


        //执行存储过程，返回SqlDataReader
        public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlDataReader returnReader;
                connection.Open();
                SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
                command.CommandType = CommandType.StoredProcedure;
                returnReader = command.ExecuteReader();
                return returnReader;
            }
        }

        //执行存储过程，返回结果集
        public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }

        }

        //执行存储过程，返回影响的行数.
        public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                int result;
                connection.Open();
                SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
                rowsAffected = command.ExecuteNonQuery();
                result = (int)command.Parameters["ReturnValue"].Value;
                //Connection.Close();
                return result;
            }
        }
    }

    #region 日志文件
    /// <summary>
    ///File_Erres 的摘要说明
    /// </summary>
    public class File_Erres
    {
        public File_Erres()
        {
            //
            //TODO: 在此处添加构造函数逻辑
            //      
        }
        public static void ERRE_File(string erre)
        {
            string paht = "C:/自动同步数据直接材料单日志/";
            if (!Directory.Exists(paht)) //如果文件夹不存在则创建
            {
                Directory.CreateDirectory(paht);
            }
            FileStream fs = new FileStream(paht + DateTime.Now.ToString("yyyy-MM-dd") + "log.txt", FileMode.OpenOrCreate, FileAccess.ReadWrite);
            StreamWriter w = new StreamWriter(fs);
            w.Flush();
            w.BaseStream.Seek(0, SeekOrigin.End);
            w.Write("\n日志项   :   ");
            w.Write("{0}   {1}   \n\n ", DateTime.Now.ToLongTimeString(),
            DateTime.Now.ToLongDateString());
            w.Write(erre + "\r\n ");
            w.Write("-------------------------------------------------- ");
            w.Write("\r\n ");
            w.Flush();
            w.Close();
        }
        /// <summary>
        /// 记事本记录错误信息
        /// </summary>
        /// <returns></returns>
        public static void ERRE_File(string Moder, string erre)
        {
            string paht = "D:/CodeErres/";
            if (!Directory.Exists(paht)) //如果文件夹不存在则创建
            {
                Directory.CreateDirectory(paht);
            }
            FileStream fs = new FileStream(paht + DateTime.Now.ToString("yyyy-MM-dd") + Moder + "log.txt", FileMode.OpenOrCreate, FileAccess.ReadWrite);
            StreamWriter w = new StreamWriter(fs);
            w.Flush();
            w.BaseStream.Seek(0, SeekOrigin.End);
            w.Write("\n日志项   :   ");
            w.Write("{0}   {1}   \n\n ", DateTime.Now.ToLongTimeString(),
            DateTime.Now.ToLongDateString());
            w.Write(erre + "\r\n ");
            w.Write("-------------------------------------------------- ");
            w.Write("\r\n ");
            w.Flush();
            w.Close();
        }
    }
    #endregion

}
